/*
 * Created on 2005-4-26
 *
 * 
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.dcivision.form.web;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableWorkbook;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.dcivision.form.bean.FormElement;
import com.dcivision.form.bean.FormPage;
import com.dcivision.form.bean.FormRecord;
import com.dcivision.form.bean.FormSection;
import com.dcivision.form.dao.FormDataElementDAObject;
import com.dcivision.form.dao.FormPageDAObject;
import com.dcivision.form.dao.FormRecordDAObject;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.SystemFunctionConstant;
import com.dcivision.framework.Utility;
import com.dcivision.framework.web.AbstractListAction;
import com.dcivision.framework.web.AbstractSearchForm;

/**
 * @author Administrator
 *
 * 
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class SearchSubmissionAction extends AbstractListAction {
  public SearchSubmissionAction() {
    super();
  }

  /**
   * getMajorDAOClassName
   *
   * @return  The class name of the major DAObject will be used in this action.
   */
  
  public String getMajorDAOClassName() {
    return ("com.dcivision.form.dao.FormSubmissionDAObject");
  }

  /**
   * getFunctionCode
   *
   * @return  The corresponding system function code of action.
   */
  
  public String getFunctionCode() {
    return (SystemFunctionConstant.FORM_SUBMISSION);
  }

  /**
   * setPageTitle
   * set the extend page title and page path.
   * default page path/title will be created by navmode and functionCode
   */
  
  public void setPageTitle(HttpServletRequest request, HttpServletResponse response, ActionForm form, ActionMapping mapping, ActionForward actionForward){
    FormRecord formRecord = (FormRecord)request.getAttribute("formRecord");
    if(!Utility.isEmpty(formRecord)){
      String extendTitle = formRecord.getTitle();
      request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE, extendTitle);
      request.setAttribute(GlobalConstant.EXTEND_PAGE_TITLE_SHOW_ONLY, new Boolean(true));
    }
  }
  
  
  public ActionForward execute(ActionMapping mapping,
                               ActionForm form,
                               HttpServletRequest request,
                               HttpServletResponse response)
      throws ServletException {
    HttpSession session = request.getSession();
    AbstractSearchForm searchForm = (AbstractSearchForm) form;
    ListFormSubmissionForm listFormSubmissionForm = (ListFormSubmissionForm) form;
    String opMode = searchForm.getOpMode();
    String navMode = searchForm.getNavMode();
    //get the formSubmissionList by formRecordID
    String formRecordID = request.getParameter("formRecordID");
    
    FormRecordDAObject formRecordDAO = new FormRecordDAObject(this.getSessionContainer(request),this.getConnection(request));
    FormPageDAObject formPageDAO = new FormPageDAObject(this.getSessionContainer(request), this.getConnection(request));
    //Set Page Title   
    FormRecord formRecord = (FormRecord)formRecordDAO.getByFormRecordID(formRecordID);
    request.setAttribute("formRecord", formRecord);
    super.setFunctionCode(SystemFunctionConstant.FORM_SUBMISSION);
    
    //  Set the function code for current action.
    ActionForward forward = this.retrieveFunctionCode(request, response, mapping);
    if (forward!=null) {
      return forward;
    }
    
    //Export Excel
    if (FormRecord.EXPORT_EXCEL_FILE.equals(request.getParameter("operationType"))){
      expToExcel(request,response,form);
      return null;       
    }
    if (FormRecord.NAV_MODE_LFT.equals(navMode)){
      return mapping.findForward(FormRecord.NAV_MODE_LFT);
    }else if (GlobalConstant.NAV_MODE_REFRESH.equals(opMode)) { //Refresh and get the UDF Details
      log.debug("enter refresh for UDF ");
      try {
        searchForm.setOpMode("");
        refreshFormData(mapping, searchForm, request, response);
      } catch (ApplicationException appEx) {
        this.rollback(request);
        handleApplicationException(request, appEx);
      }
      return mapping.findForward(GlobalConstant.NAV_MODE_LIST);
    }
    //get the formSubmissionList by search criterion
    //    get Search Panel.
    Map searchPanelMap = new HashMap();
    int textSearchFieldRowValue = !Utility.isEmpty(request.getParameter("textSearchFieldRowValue")) && !"null".equals(request.getParameter("textSearchFieldRowValue")) ? new Integer(request.getParameter("textSearchFieldRowValue")).intValue() : 1; 
    int selectionSearchFieldRowValue = !Utility.isEmpty(request.getParameter("selectionSearchFieldRowValue")) && !"null".equals(request.getParameter("selectionSearchFieldRowValue")) ? new Integer(request.getParameter("selectionSearchFieldRowValue")).intValue() : 1; 
    int datePickerSearchFieldRowValue = !Utility.isEmpty(request.getParameter("datePickerSearchFieldRowValue")) && !"null".equals(request.getParameter("datePickerSearchFieldRowValue")) ? new Integer(request.getParameter("datePickerSearchFieldRowValue")).intValue() : 1; 
    
    String[] textSearchField = request.getParameterValues("basicSearchField");
    String[] textSearchType = request.getParameterValues("basicSearchType");
    String[] textSearchKeyword = request.getParameterValues("basicSearchKeyword");
    String[] selectionSearchField = request.getParameterValues("selectionSearchField");
    String[] selectionSearchKeyword = request.getParameterValues("selectionSearchKeyword");
    String[] datePickerSearchField = request.getParameterValues("datePickerSearchField");
    String[] datePickerSearchKeywordFrom = new String[datePickerSearchFieldRowValue];
    String[] datePickerSearchKeywordTo = new String[datePickerSearchFieldRowValue];
    String[] datePickerLogic = new String[datePickerSearchFieldRowValue];
    String[] textLogic = new String[textSearchFieldRowValue];
    String[] selectionLogic = new String[selectionSearchFieldRowValue];
    
    for (int i=1; i<=datePickerSearchFieldRowValue; i++){
    	datePickerSearchKeywordFrom[i-1] = request.getParameter("datePickerSearchKeywordFrom_"+i);
    	datePickerSearchKeywordTo[i-1] = request.getParameter("datePickerSearchKeywordTo_"+i);
    	datePickerLogic[i-1] = !Utility.isEmpty(request.getParameter("datePickerLogic_"+i)) ? request.getParameter("datePickerLogic_"+i) : "";
    }
    for (int i=1; i<=textSearchFieldRowValue; i++){
    	textLogic[i-1] = !Utility.isEmpty(request.getParameter("textLogic"+i)) ? request.getParameter("textLogic"+i) : "";
    }
    for (int i=1; i<=selectionSearchFieldRowValue; i++){
    	selectionLogic[i-1] = !Utility.isEmpty(request.getParameter("selectionLogic"+i)) ? request.getParameter("selectionLogic"+i) : "";
    } 
    Object[] textBoxObject = new Object[3];
    Object[] datePickerObject = new Object[3];
    Object[] selectionObject = new Object[2];
    textBoxObject[0] = textSearchField;
    textBoxObject[1] = textSearchType;
    textBoxObject[2] = textSearchKeyword;
    //  textBoxObject[3] = textLogic;
    datePickerObject[0] = datePickerSearchField;
    datePickerObject[1] = datePickerSearchKeywordFrom;
    datePickerObject[2] = datePickerSearchKeywordTo;
    //  datePickerObject[3] = datePickerLogic;
    selectionObject[0] = selectionSearchField;
    selectionObject[1] = selectionSearchKeyword;
    // selectionObject[2] = selectionLogic;
    searchPanelMap.put("textBoxObject",textBoxObject);
    searchPanelMap.put("selectionObject",selectionObject);
    searchPanelMap.put("datePickerObject",datePickerObject);
    listFormSubmissionForm.setSearchPanelMap(searchPanelMap);
    
    this.setListName("formSubmissionListByFormRecordID");
    if ("FORMSUBMISSION".equals(request.getParameter("function"))){
    	this.setListFunctionName("getFormSubmissionListByAdvanceSearchSectionForTracking");     
    }else{
    	this.setListFunctionName("getFormSubmissionListByAdvanceSearchSectionForMonitor");
    }
    request.getSession().setAttribute(this.m_sListName, form);   
    return super.execute(mapping, form, request, response);
  }

  public void refreshFormData(ActionMapping mapping, AbstractSearchForm form, HttpServletRequest request, HttpServletResponse response) throws ApplicationException {
    SessionContainer sessionContainer = this.getSessionContainer(request);
    Connection conn = this.getConnection(request);
    
    try {
      ListFormSubmissionForm listFormSubmissionForm = (ListFormSubmissionForm) form;
      
      List resultList = request.getSession().getAttribute(this.m_sListName) == null ?
          new ArrayList() : (List) request.getSession().getAttribute(this.m_sListName);
      request.setAttribute(this.m_sListName, resultList);    
      //listFormSubmissionForm.setFunction("FORMSUBMISSION"); 
      
    } catch (Exception e) {
      log.error(e);
      throw new ApplicationException(ErrorConstant.COMMON_FATAL_ERROR, e);
    } finally {
      conn = null;
    }
  }    
  
  public void expToExcel(HttpServletRequest request,HttpServletResponse response, ActionForm form){
    try{
      
      response.setContentType("APPLICATION/OCTET-STREAM");
      response.addHeader("Content-Transfer-Encoding", "base64");
      String filename = "FormSubmission.xls";
      response.addHeader("Content-Disposition", "attachment; filename=" + filename + "\"");
      WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD);
      WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 10);
      WritableFont bodyFont = new WritableFont(WritableFont.ARIAL, 10);
      WritableFont footerFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
      WritableCellFormat titleCellFormat = new WritableCellFormat(titleFont);
      titleCellFormat.setAlignment(Alignment.RIGHT);
      WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
      WritableCellFormat bodyCellFormat = new WritableCellFormat(bodyFont);
      WritableCellFormat numberCellFormat = new WritableCellFormat(bodyFont,NumberFormats.FLOAT);
      WritableCellFormat integerCellFormat = new WritableCellFormat(bodyFont,NumberFormats.INTEGER);
      WritableCellFormat footerCellFormat = new WritableCellFormat(footerFont);
      
      jxl.write.Label    slabel;
      ServletOutputStream sos =response.getOutputStream();
      WritableWorkbook xslwork=Workbook.createWorkbook(sos);
      
      
      int rowSpaceNuber=3;
      int iCol = -1;
      int pageListSize =0;
      String formRecordID = request.getParameter("formRecordID");
      Integer pageID = new Integer(0);
      Integer sectionID = new Integer(0);     
      FormPageDAObject formPageDAO = new FormPageDAObject(this.getSessionContainer(request), this.getConnection(request));
      List formPageList = formPageDAO.getDetailListByFormRecordIDStepID(new Integer(formRecordID),new Integer(0));
      
      AbstractSearchForm searchForm = (AbstractSearchForm) form;
      ListFormSubmissionForm listFormSubmissionForm = (ListFormSubmissionForm) form;
      //display format
      String notDisplayStr = listFormSubmissionForm.getDisplayFormat();
      String elementStr =  notDisplayStr.substring(0,notDisplayStr.indexOf("|"));
      String sectionStr =  notDisplayStr.substring(notDisplayStr.indexOf("|")+1,notDisplayStr.lastIndexOf("|"));
      String pageStr = notDisplayStr.substring(notDisplayStr.lastIndexOf("|")+1,notDisplayStr.length());
      for (int n=0 ;n<formPageList.size(); n++){
        FormPage formPage = (FormPage) formPageList.get(n);
        pageID = formPage.getID();
        if (pageStr.indexOf(pageID.toString())>-1){
          continue;  
        }
        List formSectionList = formPage.getFormSectionList();
        jxl.write.WritableSheet ws=xslwork.createSheet("FormPage"+(n+1),n);
        slabel=new jxl.write.Label(3,1,formPage.getTitle());
        ws.addCell(slabel); 
        iCol = -1;
        for (int i=0; i<formSectionList.size(); i++) {
          FormSection tmpFormSection = (FormSection) formSectionList.get(i);
          sectionID = tmpFormSection.getID();  
          if (sectionStr.indexOf(sectionID.toString())>-1){
            continue;  
          }
          if (i==0 ){
            slabel=new jxl.write.Label(0,rowSpaceNuber,tmpFormSection.getTitle());       
            ws.addCell(slabel);
          }else{
            slabel=new jxl.write.Label(iCol+1,rowSpaceNuber,tmpFormSection.getTitle());       
            ws.addCell(slabel);
          }
          
          FormDataElementDAObject tmpFormDataElementDAO = new FormDataElementDAObject(this.getSessionContainer(request),this.getConnection(request));
          List formElementFormDataList = new ArrayList();
          int iRow = -1;
          try{
            //get Search Panel.
            Map searchPanelMap = new HashMap();
            int datePickerSearchFieldRowValue = !Utility.isEmpty(request.getParameter("datePickerSearchFieldRowValue")) && !"null".equals(request.getParameter("datePickerSearchFieldRowValue")) ? new Integer(request.getParameter("datePickerSearchFieldRowValue")).intValue() : 1; 
            String[] textSearchField = request.getParameterValues("basicSearchField");
            String[] textSearchType = request.getParameterValues("basicSearchType");
            String[] textSearchKeyword = request.getParameterValues("basicSearchKeyword");
            String[] selectionSearchField = request.getParameterValues("selectionSearchField");
            String[] selectionSearchKeyword = request.getParameterValues("selectionSearchKeyword");
            String[] datePickerSearchField = request.getParameterValues("datePickerSearchField");
            String[] datePickerSearchKeywordFrom = new String[datePickerSearchFieldRowValue];
            String[] datePickerSearchKeywordTo = new String[datePickerSearchFieldRowValue];
            for (int ii=1; ii<=datePickerSearchFieldRowValue; ii++){
              datePickerSearchKeywordFrom[ii-1] = request.getParameter("datePickerSearchKeywordFrom_"+ii);
              datePickerSearchKeywordTo[ii-1] = request.getParameter("datePickerSearchKeywordTo_"+ii);
            }
            Object[] textBoxObject = new Object[3];
            Object[] datePickerObject = new Object[3];
            Object[] selectionObject = new Object[2];
            textBoxObject[0] = textSearchField;
            textBoxObject[1] = textSearchType;
            textBoxObject[2] = textSearchKeyword;
            datePickerObject[0] = datePickerSearchField;
            datePickerObject[1] = datePickerSearchKeywordFrom;
            datePickerObject[2] = datePickerSearchKeywordTo;
            selectionObject[0] = selectionSearchField;
            selectionObject[1] = selectionSearchKeyword;
            searchPanelMap.put("textBoxObject",textBoxObject);
            searchPanelMap.put("selectionObject",selectionObject);
            searchPanelMap.put("datePickerObject",datePickerObject);
            listFormSubmissionForm.setSearchPanelMap(searchPanelMap);
            if ("FORMSUBMISSION".equals(request.getParameter("function"))){
              formElementFormDataList = tmpFormDataElementDAO.getFormElementFormDataListByPageIDSectionIDAdvanceSearch(pageID,sectionID,searchForm,true);
            }else{
              formElementFormDataList = tmpFormDataElementDAO.getFormElementFormDataListByPageIDSectionIDAdvanceSearch(pageID,sectionID,searchForm,false);
            }           
          } catch (ApplicationException appEx) {
          }
          String tmpFieldID = "";   
          iRow = rowSpaceNuber+1; 
          for (int ii=0; ii<formElementFormDataList.size(); ii++){    
            Object[] objFormElementFormData = null;
            objFormElementFormData = (Object[])formElementFormDataList.get(ii);
            if(tmpFieldID.equals(objFormElementFormData[2].toString()) ){        
                iRow ++;    
                if (!Utility.isEmpty(objFormElementFormData[6]) && FormElement.VALIDATION_INTEGER_ONLY.equals(objFormElementFormData[6]) ){
                    jxl.write.Number integer = new jxl.write.Number(iCol,iRow,Float.parseFloat(objFormElementFormData[5].toString()),integerCellFormat);           
                    ws.addCell(integer);
                }else if (!Utility.isEmpty(objFormElementFormData[6]) && FormElement.VALIDATION_NUMERIC_ONLY.equals(objFormElementFormData[6]) ){
                    jxl.write.Number number = new jxl.write.Number(iCol,iRow,Float.parseFloat(objFormElementFormData[5].toString()),numberCellFormat);           
                    ws.addCell(number);
                }else{ 
                    slabel=new jxl.write.Label(iCol,iRow,Utility.isEmpty(objFormElementFormData[5])?"":objFormElementFormData[5].toString());
                    ws.addCell(slabel);
                }
            } else {
                iCol ++;
                iRow = rowSpaceNuber+1; 
                slabel=new jxl.write.Label(iCol,iRow,objFormElementFormData[4].toString());
                ws.addCell(slabel);            
                iRow ++;
                if (!Utility.isEmpty(objFormElementFormData[6]) && FormElement.VALIDATION_INTEGER_ONLY.equals(objFormElementFormData[6]) ){
                    jxl.write.Number integer = new jxl.write.Number(iCol,iRow,Float.parseFloat(objFormElementFormData[5].toString()),integerCellFormat);           
                    ws.addCell(integer);
                }else if (!Utility.isEmpty(objFormElementFormData[6]) && FormElement.VALIDATION_NUMERIC_ONLY.equals(objFormElementFormData[6]) ){
                    jxl.write.Number number = new jxl.write.Number(iCol,iRow,Float.parseFloat(objFormElementFormData[5].toString()),numberCellFormat);           
                    ws.addCell(number);
                }else{ 
                    slabel=new jxl.write.Label(iCol,iRow,Utility.isEmpty(objFormElementFormData[5])?"":objFormElementFormData[5].toString());
                    ws.addCell(slabel);
                }
                tmpFieldID = Utility.isEmpty(objFormElementFormData[2])?"":objFormElementFormData[2].toString();
            }
            
          }         
        }      
      }
      
      xslwork.write();
      xslwork.close();
      sos.close();
    } catch(Exception e){
       log.error(e, e);
    }
  }
}